package org.vuzya.motivateme.db;

import static org.vuzya.motivateme.helpers.ConstantHelper.dateFormater;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

import org.vuzya.motivateme.helpers.ConstantHelper;
import org.vuzya.motivateme.models.*;

public class DBHelper extends SQLiteOpenHelper {

	private static final String BUSINESS_CREATION_SCRIPT = new StringBuilder(300)
			.append("CREATE TABLE `"+Businesses.TABLE_NAME+"` (")
			.append("`"+Businesses._ID+"` INTEGER PRIMARY KEY AUTOINCREMENT,")
			.append("`"+Businesses.TITLE+"` VARCHAR(50),")
			.append("`"+Businesses.DESCRIPTION+"` VARCHAR(500),")
			.append("`"+Businesses.START_DATE+"` DATE,")
			.append("`"+Businesses.FINISH_DATE+"` DATE")
			.append(");").toString();
	private static final String MOTIVATION_CREATION_SCRIPT = new StringBuilder(200)
			.append("CREATE TABLE `"+Motivations.TABLE_NAME+"` (")
			.append("`"+Motivations._ID+"` INTEGER PRIMARY KEY AUTOINCREMENT,")
			.append("`"+Motivations.MOTIVATION_DATE+"` DATE,")
			.append("`"+Motivations.BUSINESS_ID+"` INTEGER, ")
			.append("CONSTRAINT `Motivations_fk1` FOREIGN KEY (`"+Motivations.BUSINESS_ID+"`) REFERENCES "+Businesses.TABLE_NAME+"(`"+Businesses._ID+"`)")
			.append(");").toString();
	private static final String ANSWERS_CREATION_SCRIPT = new StringBuilder(200)
			.append("CREATE TABLE `"+QuestionAnswers.TABLE_NAME+"` (")
			.append("`"+QuestionAnswers._ID+"` INTEGER PRIMARY KEY AUTOINCREMENT,")
			.append("`"+QuestionAnswers.QUESTION_NUMBER+"` INTEGER,")
			.append("`"+QuestionAnswers.ANSWER+"` VARCHAR,")
			.append("`"+QuestionAnswers.MOTIVATION_ID+"` INTEGER, ")
			.append("CONSTRAINT `QuestionAnswers_fk1` FOREIGN KEY (`"+QuestionAnswers.MOTIVATION_ID+"`) REFERENCES "+Motivations.TABLE_NAME+"(`"+Motivations._ID+"`)")
			.append(");").toString();
	//private static final String FOREIGN_KEYS_ON = "PRAGMA foreign_keys = ON;";
			
	//private static final String UPDATE_TO_V2 = "";
	private static final int VERSION = 1;
	private static final String DATABASE_NAME = "motivateme.db";
	
	private SQLiteDatabase db = null;
	
	public DBHelper(Context context) {
		super(context, DATABASE_NAME, null, VERSION);
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		db.execSQL(BUSINESS_CREATION_SCRIPT);
		Log.d(DATABASE_NAME, "Business table created");
		
		db.execSQL(MOTIVATION_CREATION_SCRIPT);
		Log.d(DATABASE_NAME, "Motivation table created");
		
		db.execSQL(ANSWERS_CREATION_SCRIPT);
		Log.d(DATABASE_NAME, "Aswers table created");
		
		Log.d(DATABASE_NAME, "Database created!");
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
//		if(oldVersion==1){
//			db.execSQL(sql);
//			oldVersion++;
//		}
		Log.d(DATABASE_NAME, String.format("Database updated from %d to %d",oldVersion,newVersion));
	}
	
	public Cursor getAllBusinesses(){
		initDb();
		return db.query(Businesses.TABLE_NAME, Businesses.SELECTION, null, null, null, null, Businesses.START_DATE + " DESC");
	}
	
	public Cursor getBusinesses(boolean isFinished){
		initDb();
		String where; 
		if(isFinished){
			where = Businesses.FINISH_DATE + " is not null";
		}else{
			where = Businesses.FINISH_DATE + " is null";
		}
		return db.query(Businesses.TABLE_NAME, Businesses.SELECTION, where, null, null, null, Businesses.START_DATE + " DESC");
	}
	
	public Cursor getBusiness(long id){
		initDb();
		
		return db.query(Businesses.TABLE_NAME, Businesses.SELECTION, Businesses._ID + " = ?", new String[] { Long.toString(id) }, null, null, Businesses.START_DATE + " DESC");
	}
	
	public Cursor getBusinessMotivations(long businessId){
		initDb();
		final String WHERE;
		if(businessId != -1){
			WHERE = Motivations.BUSINESS_ID + " = " + Long.toString(businessId);
		}else{
			WHERE = null;
		}
		return db.query(Motivations.TABLE_NAME, Motivations.SELECTION, WHERE, null, null, null, Motivations.MOTIVATION_DATE + " DESC");
	}
	
	public Cursor getMotivationQuestionAnswers(long motivationId){
		initDb();
		final String WHERE;
		if(motivationId != -1){
			WHERE = QuestionAnswers.MOTIVATION_ID + " = " + Long.toString(motivationId);
		}else{
			WHERE = null;
		}
		return db.query(QuestionAnswers.TABLE_NAME, QuestionAnswers.SELECTION, WHERE, null, null, null, QuestionAnswers.QUESTION_NUMBER);
	}
	
	public Cursor getQuestionAnswers(long businessId){
		initDb();
		final String[] columns = new String[]{
			QuestionAnswers.MOTIVATION_ID,
			QuestionAnswers.ANSWER,
			QuestionAnswers.QUESTION_NUMBER,
			Motivations.MOTIVATION_DATE	
		};
		String join = new StringBuilder()
			.append(Motivations.TABLE_NAME)
			.append(" INNER JOIN ")
			.append(QuestionAnswers.TABLE_NAME)
			.append(" ON (")
			.append(Motivations.TABLE_NAME)
			.append('.')
			.append(Motivations._ID)
			.append(" = ")
			.append(QuestionAnswers.TABLE_NAME)
			.append('.')
			.append(QuestionAnswers.MOTIVATION_ID)
			.append(')')
			.toString();
		String orderBy = new StringBuilder()
			.append(QuestionAnswers.QUESTION_NUMBER)
			.append(", ")
			.append(Motivations.MOTIVATION_DATE)
			.toString();
		return db.query(join, columns, Motivations.BUSINESS_ID + " = ?", new String[] { Long.toString(businessId) }, null,	null, orderBy);
	}

	public List<Businesses> getBusinessesModels(){
		List<Businesses> list = new ArrayList<Businesses>();
		Cursor cursor = getAllBusinesses();
		if(!cursor.moveToFirst()){
			return list;
		}
		do{
			list.add(new Businesses(cursor));
		}while(cursor.moveToNext());
		
		return list;
	}
	
	public List<Motivations> getMotivationsModels(long businessId){
		List<Motivations> list = new ArrayList<Motivations>();
		Cursor cursor = getBusinessMotivations(businessId);
		if(!cursor.moveToFirst()){
			return list;
		}
		do{
			list.add(new Motivations(cursor));
		}while(cursor.moveToNext());
		
		return list;
	}
	
	public List<QuestionAnswers> getQuestionAnswersModels(long motivationId){
		List<QuestionAnswers> list = new ArrayList<QuestionAnswers>();
		Cursor cursor = getMotivationQuestionAnswers(motivationId);
		if(!cursor.moveToFirst()){
			return list;
		}
		do{
			list.add(new QuestionAnswers(cursor));
		}while(cursor.moveToNext());
		
		return list;
	}
	
//	public void addBusiness(Businesses b){
//		initDb();
//		db.insertOrThrow(Businesses.TABLE_NAME, null, b.extractValues());
//	}
	
	public long addBusiness(String title, String description, String date){
		initDb();
		ContentValues cv = new ContentValues();
		cv.put(Businesses.TITLE, title);
		cv.put(Businesses.DESCRIPTION, description);
		cv.put(Businesses.START_DATE, date);
		return db.insertOrThrow(Businesses.TABLE_NAME, null, cv);
	}
	
//	public void addMotivation(Motivations m){
//		initDb();
//		db.insertOrThrow(Motivations.TABLE_NAME, null, m.extractValues());
//	}
	
	public long addMotivation(long businessId, String[] answers){
		initDb();
		String date = dateFormater.format(new Date());
		ContentValues cv = new ContentValues();
		cv.put(Motivations.MOTIVATION_DATE, date);
		cv.put(Motivations.BUSINESS_ID, businessId);
		final long motivationId = db.insert(Motivations.TABLE_NAME, null, cv);
		for(int i=0;i<answers.length;i++){
			cv = new ContentValues();
			cv.put(QuestionAnswers.ANSWER, answers[i]);
			cv.put(QuestionAnswers.QUESTION_NUMBER, i);
			cv.put(QuestionAnswers.MOTIVATION_ID, motivationId);
			db.insertOrThrow(QuestionAnswers.TABLE_NAME, null, cv);
		}
		return motivationId;
	}
	
//	public void addQuestionAnswer(QuestionAnswers qa){
//		initDb();
//		db.insertOrThrow(QuestionAnswers.TABLE_NAME, null, qa.extractValues());
//	}
	
	public int updateBusiness(long id, String title, String description, String date){
		initDb();
		ContentValues cv = new ContentValues();
		cv.put(Businesses.TITLE, title);
		cv.put(Businesses.DESCRIPTION, description);
		cv.put(Businesses.START_DATE, date);
		return db.update(Businesses.TABLE_NAME, cv, Businesses._ID + " = ?", new String[]{ Long.toString(id) });
	}
	
	public void deleteBusiness(long id){
		initDb();
		db.delete(Businesses.TABLE_NAME, Businesses._ID + "=?", new String[] { Long.toString(id) });
	}
	
	public void businessIsDone(long id){
		initDb();
		Date d = new Date();
		ContentValues values = new ContentValues();
		values.put(Businesses.FINISH_DATE, ConstantHelper.dateFormater.format(d));
		db.update(Businesses.TABLE_NAME, values, Businesses._ID + "=?", new String[] { Long.toString(id) });
	}
	
	public void businessIsUndone(long id){
		initDb();
		ContentValues values = new ContentValues();
		String _null = null;
		values.put(Businesses.FINISH_DATE, _null);
		db.update(Businesses.TABLE_NAME, values, Businesses._ID + "=?", new String[] { Long.toString(id) });
	}
	
	private void initDb() {
		if(db == null) {
			db = this.getWritableDatabase();
		}
	}
	
	public void recreate(){
		initDb();
		try {
			db.execSQL("DROP TABLE `" + Businesses.TABLE_NAME + "`;");
			Log.d(DATABASE_NAME, Businesses.TABLE_NAME + " droped");
		} catch (Exception e) {
			Log.d(DATABASE_NAME, Businesses.TABLE_NAME + " NOT droped");
		}
		
		try {
			db.execSQL("DROP TABLE `" + Motivations.TABLE_NAME + "`;");
			Log.d(DATABASE_NAME, Motivations.TABLE_NAME + " droped");
		} catch (Exception e) {
			Log.d(DATABASE_NAME, Motivations.TABLE_NAME + " NOT droped");
		}
		
		try {
			db.execSQL("DROP TABLE `" + QuestionAnswers.TABLE_NAME + "`;");
			Log.d(DATABASE_NAME, QuestionAnswers.TABLE_NAME + " droped");
		} catch (Exception e) {
			Log.d(DATABASE_NAME, QuestionAnswers.TABLE_NAME + " NOT droped");
		}
		
		try{
			onCreate(db);
		}catch (Exception e) {
			Log.d(DATABASE_NAME, "Creating failed");
		}
	}
	
	@Override
	public synchronized void close() {
		db.close();
		super.close();
	}
}
